<?php
namespace Home\Controller\PublicAction;
use Think\Controller;
/*
 * 订单管理 2016.6.14
 * author:candy
 * 
 */
class OrderController extends Controller {

    public function index(){
      // $this->display('Test:testorder');
        // $this->getOrderById(session('user.user_id'));
        $this->getOrderById(11);
        // $this->show('<style type="text/css">*{ padding: 0; margin: 0; } div{ padding: 4px 48px;} body{ background: #fff; font-family: "微软雅黑"; color: #333;font-size:24px} h1{ font-size: 100px; font-weight: normal; margin-bottom: 12px; } p{ line-height: 1.8em; font-size: 36px } a,a:hover{color:blue;}</style><div style="padding: 24px 48px;"> <h1>:)</h1><p>欢迎使用 <b>ThinkPHP</b>！</p><br/>版本 V{$Think.version}</div><script type="text/javascript" src="http://ad.topthink.com/Public/static/client.js"></script><thinkad id="ad_55e75dfae343f5a1"></thinkad><script type="text/javascript" src="http://tajs.qq.com/stats?sId=9347272" charset="UTF-8"></script>','utf-8');
    }

    public function test(){
      $this->display('Test:testorder');
    }

    public function submit(){
        $order_id = $this->addOrder();
        $this->addOrderItem($order_id);
    }

    /**
     * 6.5 
     * 订单添加 测试Order 表
     * 
     */ 
    public function addOrder(){
        $Order = D('Order');
        $Order->create();
        date_default_timezone_set("Asia/Shanghai");
        $Order->datetime = date('y-m-d h:i:s',time());
        return $Order->add();
    }

    /**
     * 6.14 
     * 根据用户id 获取订单
     * 7.9分组
     */
    public function getOrderById($user=null){
        $Order = M('Order');
        $result=$Order
        ->join('order_item ON order.order_id = order_item.order_id')
        ->join('item ON item.item_id = order_item.item_id')
        ->join('good ON good.good_id = item.good_id')
        ->join('picture ON picture.id = item.good_id')
        ->order('datetime desc')
        ->group('order.order_id,item.item_id')
        ->where('order.user_id="'.$user.'"')
        ->select();
        // $result = $Order
        // ->join('order_item I on order.order_id = I.order_id')
        // ->order('datetime desc')
        // ->where('buyer="'.$user.'"')->select();
        //分组
        $orders = array();
        $current;
        $order = array();
        if(count($result)>0){

          foreach ($result as $key => $item) {
            if(!$current||$item['order_id']==$current['order_id']){
              array_push($order,$item);
              $current = $item;
            }else{
              array_push($orders,array('order_id'=>$current['order_id'],'items'=>$order));
              $order = array();
              array_push($order,$item);
              $current = $item;
            }
          }
          array_push($orders,array('order_id'=>$current['order_id'],'items'=>$order,'time'=>$current['datetime']));
          // dump($orders);
          $this->assign('orders',$orders);
        }
        $this->display("User:order");
    }

    public function arguelist($status=1){
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Easy set variables
         */
         //status 6 代表争议订单
        // DB table to use
        $table ='order';
         
        // Table's primary key
        $primaryKey = 'order_id';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => '`o`.`order_no`', 'dt' => 'orderno' , 'field' => 'order_no'),
            array( 'db' => '`o`.`order_id`', 'dt' => 'orderid' , 'field' => 'order_id'),
            array( 'db' => '`s`.`name`',  'dt' => 'sname' ,  'field' => 'sname', 'as'=>'sname'),
            array( 'db' => '`u`.`name`',   'dt' => 'uname' ,  'field' => 'uname', 'as'=>'uname'),
            array( 'db' => '`o`.`datetime`',     'dt' => 'datetime' ,  'field' => 'datetime')
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = " AND `o`.`datetime` between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        $joinQuery = "FROM `{$table}` AS `o` LEFT JOIN `shop` AS `s` ON (`s`.`shop_id` = `o`.`shop_id`) LEFT JOIN `user` AS `u` ON (`u`.`user_Id` = `o`.`user_id`)";
        $extraCondition = "`o`.`status`=".$status.$where;
        Vendor('SSP');
        echo json_encode(
               \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraCondition)
             );
    }

    public function getOrderDetail($id=null){
      $Order = M('Order');
      $result=$Order
      ->join('order_item ON order.order_id = order_item.order_id')
      ->join('item ON item.item_id = order_item.item_id')
      ->join('good ON good.good_id = item.good_id')
      ->join('picture ON picture.id = item.good_id')
      ->order('datetime desc')
      ->group('order.order_id,item.item_id')
      ->where('order.order_id="'.$id.'"')->select();

      $info = $Order
      ->join('shop ON shop.shop_id = order.shop_id','left')
      ->join('address ON address.address_id = order.address_id','left')
      ->order('datetime desc')
      ->where('order.order_id="'.$id.'"')->select();
      $this->assign('info',$info[0]);//收货信息 店家信息
      $this->assign('result',$result);//订单商品信息
      $this->display('User:orderdetail');
    }

    /**      
      * 6.5 
      * 订单项添加 order_item表
      * 完成多规格
     */
    private function addOrderItem($order_id=null){
        $OrderItem = M("OrderItem");
        $dataList = array();
        for ($i=0; $i < count($_POST['number']); $i++) { 
            $dataList[] = array(
                    'number'=>$_POST['number'][$i],
                    'item_id'=>$_POST['item_id'][$i],
                    'order_id'=>$order_id
                );
        }
        return $OrderItem->addAll($dataList);
    }

  /**
   * 8.6
   * 订单统计
   * 按月份统计
   */
    public function analysis(){
      $number = 0;
      $sum = 0;
      $start=I('post.start');
      $end = I('post.end');
      if($start&&$end){
        $Order = M("Order");
        $map['datetime'] = array('between',array($start,$end));    
        $number = $Order->where($map)->count();  
        $sum = $Order->where($map)->sum('sum');  
      }else{
        $Order = M("Order");
        $map['datetime'] = array('between',array("2016-07-06","2016-08-06"));
        $number = $Order->where($map)->count();  
        $sum = $Order->where($map)->sum('sum');  
      }
      if($sum===null)$sum = 0;
      echo json_encode(array('number'=>$number,'sum'=>$sum,'start'=>$start,'end'=>$end));
    }

    // public function lastmonths(){
    //   $Order = M('Order');
    //   $start=I('post.start');
    //   $end = I('post.end');
    //   if($start&&$end){
    //     $result = $Order->query("select DATE_FORMAT(datetime,'%m') as Times,sum(sum) as Totals from `order` where datetime between '"
    //       .$start."' and '".$end."' group by DATE_FORMAT(datetime,'%m')");
        
    //     $new_result = array();
    //     foreach ($result as $key => $value) {
    //        $new_result[] = array_values($value);
    //     }
    //     echo json_encode($new_result);
    //   }
    // }    

    public function lastmonths(){
      $Order = M('Order');
      $start=I('post.start');
      $end = I('post.end');

      if($start!=''&&$end!=''){
          $sql = "DROP PROCEDURE IF EXISTS make_intervals;
        CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
        BEGIN
        -- *************************************************************************
        -- Procedure: make_intervals()
        --    Author: Ron Savage
        --      Date: 02/03/2009
        --
        -- Description:
        -- This procedure creates a temporary table named time_intervals with the
        -- interval_start and interval_end fields specifed from the startdate and
        -- enddate arguments, at intervals of intval (unitval) size.
        -- *************************************************************************
           declare thisDate timestamp;
           declare nextDate timestamp;
           set thisDate = startdate;

           -- *************************************************************************
           -- Drop / create the temp table
           -- *************************************************************************
           drop temporary table if exists time_intervals;
           create temporary table if not exists time_intervals
              (
              interval_start timestamp,
              interval_end timestamp
              );

           -- *************************************************************************
           -- Loop through the startdate adding each intval interval until enddate
           -- *************************************************************************
           repeat
              select
                 case unitval
                    when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
                    when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
                    when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
                    when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
                    when 'DAY'         then timestampadd(DAY, intval, thisDate)
                    when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
                    when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
                    when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
                    when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
                 end into nextDate;

              insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
              set thisDate = nextDate;
           until thisDate >= enddate
           end repeat;

         END;";
        $Order->execute($sql);
        $sql = "call make_intervals('".$start."','".$end."',1,'MONTH')";
        $Order->execute($sql);
        $result = $Order->query("select DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') as Times,IFNULL(sum(sum),0) as Totals from `order` RIGHT JOIN `time_intervals`
        ON DATE_FORMAT(datetime,'%y-%m')= DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') where interval_start between '"
          .$start."' and '".$end."'  group by  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m')");
        $new_result = array();
        foreach ($result as $key => $value) {
           $new_result[] = array_values($value);
        }
        echo json_encode($new_result);
      }
    }    
    public function lastorders(){
      $Order = M('Order');
      $start=I('post.start');
      $end = I('post.end');

      if($start!=''&&$end!=''){
          $sql = "DROP PROCEDURE IF EXISTS make_intervals;
        CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
        BEGIN
        -- *************************************************************************
        -- Procedure: make_intervals()
        --    Author: Ron Savage
        --      Date: 02/03/2009
        --
        -- Description:
        -- This procedure creates a temporary table named time_intervals with the
        -- interval_start and interval_end fields specifed from the startdate and
        -- enddate arguments, at intervals of intval (unitval) size.
        -- *************************************************************************
           declare thisDate timestamp;
           declare nextDate timestamp;
           set thisDate = startdate;

           -- *************************************************************************
           -- Drop / create the temp table
           -- *************************************************************************
           drop temporary table if exists time_intervals;
           create temporary table if not exists time_intervals
              (
              interval_start timestamp,
              interval_end timestamp
              );

           -- *************************************************************************
           -- Loop through the startdate adding each intval interval until enddate
           -- *************************************************************************
           repeat
              select
                 case unitval
                    when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
                    when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
                    when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
                    when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
                    when 'DAY'         then timestampadd(DAY, intval, thisDate)
                    when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
                    when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
                    when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
                    when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
                 end into nextDate;

              insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
              set thisDate = nextDate;
           until thisDate >= enddate
           end repeat;

         END;";
        $Order->execute($sql);
        $sql = "call make_intervals('".$start."','".$end."',1,'MONTH')";
        $Order->execute($sql);
        $result = $Order->query("select DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') as Times,COUNT(*) as number from `order` RIGHT JOIN `time_intervals`
          ON DATE_FORMAT(datetime,'%y-%m')= DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m') where interval_start between '"
          .$start."' and '".$end."'  group by  DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(interval_start)),'%y-%m')");
        $new_result = array();
        foreach ($result as $key => $value) {
           $new_result[] = array_values($value);
        }
        echo json_encode($new_result);
      }
    }

   /**
     * 数组转xls格式的excel文件
     * @param  array  $data      需要生成excel文件的数组
     * @param  string $filename  生成的excel文件名
     *      示例数据：
            $data = array(
                array(NULL, 2010, 2011, 2012),
                array('Q1',   12,   15,   21),
                array('Q2',   56,   73,   86),
                array('Q3',   52,   61,   69),
                array('Q4',   30,   32,    0),
               );
     */
    function create_xls($data,$filename='纠纷订单.xls'){
        ini_set('max_execution_time', '0');
        Vendor('PHPExcel');
        $filename=str_replace('.xls', '', $filename).'.xls';
        $phpexcel = new \PHPExcel();
        $phpexcel->getProperties()
            ->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        $phpexcel->getActiveSheet()->fromArray($data);
        $phpexcel->getActiveSheet()->setTitle('Sheet1');
        $phpexcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$filename");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objwriter = \PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $objwriter->save('php://output');
        exit;
    }

    public function genxls(){
       /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Easy set variables
         */
         
        // DB table to use
          $table ='order';
         
        // Table's primary key
        $primaryKey = 'order_id';
         
        // Array of database columns which should be read and sent back to DataTables.
        // The `db` parameter represents the column name in the database, while the `dt`
        // parameter represents the DataTables column identifier. In this case object
        // parameter names
        $columns = array(
            array( 'db' => '`o`.`order_no`', 'dt' => 'orderno' , 'field' => 'order_no'),
            // array( 'db' => '`o`.`order_id`', 'dt' => 'orderid' , 'field' => 'order_id'),
            array( 'db' => '`s`.`name`',  'dt' => 'sname' ,  'field' => 'sname', 'as'=>'sname'),
            array( 'db' => '`u`.`name`',   'dt' => 'uname' ,  'field' => 'uname', 'as'=>'uname'),
            array( 'db' => '`o`.`datetime`',     'dt' => 'datetime' ,  'field' => 'datetime')
        );
         
        // SQL server connection information
        $sql_details = array(
            'user' => C('DB_USER'),
            'pass' => C('DB_PWD'),
            'db'   => C('DB_NAME'),
            'host' => C('DB_HOST')
        );

        if(I('get.startdate')&&I('get.enddate'))
            $where = " AND `o`.`datetime` between '".I('get.startdate')."' AND '".I('get.enddate')."'";
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP
         * server-side, there is no need to edit below this line.
         */
        $joinQuery = "FROM `{$table}` AS `o` LEFT JOIN `shop` AS `s` ON (`s`.`shop_id` = `o`.`shop_id`) LEFT JOIN `user` AS `u` ON (`u`.`user_Id` = `o`.`user_id`)";
        $extraCondition = "`o`.`status`=1".$where;
        Vendor('SSP');
        $title = array(array('订单编号','店家名','买家名','创建日期'));
        $data = \SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraCondition);
        $this->create_xls(array_merge($title,$data['data']));
    }


}